********************************************************************************
*   Project: The Diabetes care continuum in Venezuela						   *
*   Task: Table 1: DM BL & FU Pop						                       *
*	Author: Dina Goodman (Harvard University)      							   *
*	Code check:  				  											   *
*   Date: 22 July 2021													 	   *
*	Last updated: 28 June 2021								   				   *
********************************************************************************

/*Table 1 Code*/
//DM only version:
keep if clindia_self_bl==1

global vars agecat ses urban bmicat_bl htn_bl highldl_bl
*check for ps - exact if any cells <=5
tab age3cat female, col chi2
tab ses female, col chi2
tab urban female, col chi2
tab overweight_bl female, col chi2
tab htn_bl female, col chi2
tab highldl_bl female, col chi2


*Add headers
	cd "$Results"
capture rm "table1_output.xls"
putexcel set "table1_output.xls", replace
	
	putexcel B1=("Total")
	putexcel B2=("n")
	putexcel C2=("%")
	putexcel D1=("Male")
	putexcel D2=("n")
	putexcel E2=("%")
	putexcel F1=("Female")
	putexcel F2=("n")
	putexcel G2=("%")

*Total Numbers
count
	putexcel B3 = (r(N))
	putexcel C3 = ("100")
count if female==0
	putexcel D3= (r(N)) 
	putexcel E3= ("34%")
count if female==1
	putexcel F3= (r(N)) 
	putexcel G3= ("66%")
	
*Age category at baseline
putexcel A4=("Age")
local row=4
	foreach var in age3cat {
		tabulate `var', matcell(`var'freq) matrow(`var'names)
			local ++row
			putexcel A`row'= matrix(`var'names) 
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
	
		*for men
		tabulate `var' if female==0, matcell(`var'freq) matrow(`var'names)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
		*for women
		tabulate `var' if female==1, matcell(`var'freq) matrow(`var'names)
			putexcel F`row'=matrix(`var'freq) 
			putexcel G`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}		
					
*SES
putexcel A`row'= ("SES")
	foreach var in ses {
		tabulate `var', matcell(`var'freq) matrow(`var'names)
			local ++row
			putexcel A`row'= matrix(`var'names) 
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
		
		*for men
		tabulate `var' if female==0, matcell(`var'freq)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
		*for women
		tabulate `var' if female==1, matcell(`var'freq) 
			putexcel F`row'=matrix(`var'freq) 
			putexcel G`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}		

*Urban
putexcel A`row'=("Urban")
	foreach var in urban {
		tabulate `var', matcell(`var'freq) matrow(`var'names)
			local ++row
			putexcel A`row'= matrix(`var'names) 
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
		
		*for men
		tabulate `var' if female==0, matcell(`var'freq)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
		*for women
		tabulate `var' if female==1, matcell(`var'freq) 
			putexcel F`row'=matrix(`var'freq) 
			putexcel G`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}		
			
*Overweight+ BMI
putexcel A`row'=("Overweight")
	foreach var in overweight_bl {
		tabulate `var', matcell(`var'freq) matrow(`var'names)
			local ++row
			putexcel A`row'= matrix(`var'names) 
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
		
		*for men
		tabulate `var' if female==0, matcell(`var'freq)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
		*for women
		tabulate `var' if female==1, matcell(`var'freq) 
			putexcel F`row'=matrix(`var'freq) 
			putexcel G`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}		
			
*Hypertension
putexcel A`row'=("Hypertension")
	foreach var in htn_bl {
		tabulate `var', matcell(`var'freq) matrow(`var'names)
			local ++row
			putexcel A`row'= matrix(`var'names) 
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
		
		*for men
		tabulate `var' if female==0, matcell(`var'freq)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
		*for women
		tabulate `var' if female==1, matcell(`var'freq) 
			putexcel F`row'=matrix(`var'freq) 
			putexcel G`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}			

*High LDL
putexcel A`row'=("High LDL")
	foreach var in highldl_bl {
		tabulate `var', matcell(`var'freq) matrow(`var'names)
			local ++row
			putexcel A`row'= matrix(`var'names) 
			putexcel B`row'=matrix(`var'freq) 
			putexcel C`row'=matrix(`var'freq/r(N)), nformat(percent) 
		
		*for men
		tabulate `var' if female==0, matcell(`var'freq)
			putexcel D`row'=matrix(`var'freq) 
			putexcel E`row'=matrix(`var'freq/r(N)), nformat(percent) 
		*for women
		tabulate `var' if female==1, matcell(`var'freq) 
			putexcel F`row'=matrix(`var'freq) 
			putexcel G`row'=matrix(`var'freq/r(N)), nformat(percent) 
			
			local rows = rowsof(`var'names)
			forval i=1/`rows'   {
				local val = `var'names[`i',1]
				local val_lab : label (`var') `val'
				putexcel A`row'=("`val_lab'")
				local ++row
				di "row is `row'"
				}
			}		